﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DynORM.Interfaces;
using DynORM.TypeMappingSubsystem;
using DynORM.Enums;
using System.Globalization;
using DynORM.DAL.Interfaces;
using DynORM.ValueObjects;
using DynORM.ReferenceObjects;

namespace DynORM.DAL
{
    public class DAL_MSSQL : IDAL
    {
        public const int DEFAULT_CHARACTER_MAXIMUM_LENGTH = 128;
        public const int DEFAULT_NUMERIC_PRECISION = 15;
        public const int DEFAULT_NUMERIC_SCALE = 3;


        #region DDL
        public string ChangeUserInConnectionString(string connectionString, string login, string password)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            csb.UserID = login;
            csb.Password = password;
            return csb.ConnectionString;
        }

        public string ChangeDBInConnectionString(string connectionString, string databaseName)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            csb.InitialCatalog = databaseName;
            return csb.ConnectionString;
        }

        public bool TryConnect(string connectionString)
        {
            bool result = false;

            SqlConnection dbmsCon = new SqlConnection(connectionString);
            try
            {
                dbmsCon.Open();
                result = true;
            }
            catch
            {
                result = false;
            }
            finally
            {
                if (dbmsCon != null) dbmsCon.Dispose();
            }
            return result;
        }

        public void CreateDB(string connectionString, string dbName, params string[] auxData)
        {
            string connectionStringToDataBase = ChangeDBInConnectionString(connectionString, dbName);
            string connectionStringToServer = ChangeDBInConnectionString(connectionString, "master");
            if (TryConnect(connectionStringToDataBase) == false)
                using (SqlConnection con = new SqlConnection(connectionStringToServer))
                {
                    con.Open();
                    SqlCommand cmdCheck = new SqlCommand(String.Format(@"CREATE DATABASE {0}", Enquote(dbName)), con);
                    cmdCheck.ExecuteScalar();
                }
            //it takes some time server to create database. do not return method until db has created. 
            //otherwise if some method followed this one will try to connect to db exception occure 
            while (TryConnect(connectionStringToDataBase) == false)
                System.Threading.Thread.Sleep(10);
        }

        public void DropDB(string connectionString, string dbName, params string[] auxData)
        {
            string connectionStringToServer = ChangeDBInConnectionString(connectionString, "master");
            using (SqlConnection con = new SqlConnection(connectionStringToServer))
            {
                con.Open();
                SqlCommand cmdCheck = new SqlCommand(String.Format(@"DROP DATABASE {0}", Enquote(dbName)), con);
                cmdCheck.ExecuteScalar();
            }
        }

        public bool CheckTable(string connectionString, string tableName)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmdCheck = new SqlCommand(String.Format(@"SELECT 1
                                                                    FROM   information_schema.tables 
                                                                    WHERE  table_schema = 'dbo'
                                                                    AND    table_name = '{0}'", tableName), con);
                //alternative approach
                //SqlCommand cmdCheck = new SqlCommand(String.Format(@"select OBJECT_ID('[dbo].[{0}]', 'U')", tableName), con);
                //if (cmdCheck.ExecuteScalar() is System.DBNull)
                if (cmdCheck.ExecuteScalar() == null)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        public bool TableContainsAnyRecord(string connectionString, string tableName)
        {
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                SqlCommand cmdCheck = new SqlCommand(String.Format(@"SELECT COUNT(1)
                                                                    FROM   {0}", Enquote(tableName)), con);
                if ((int)cmdCheck.ExecuteScalar() != 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public bool TableContainsColumn(string connectionString, string tableName, string columnName, string columnType)
        {
            bool result = false;
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT C.COLUMN_NAME
                                                        FROM INFORMATION_SCHEMA.COLUMNS AS C
                                                        WHERE C.TABLE_NAME = '{0}' AND C.TABLE_SCHEMA='dbo' AND C.TABLE_CATALOG = '{3}'
                                                        AND C.COLUMN_NAME = '{1}' AND C.DATA_TYPE = '{2}'", tableName, columnName, columnType, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                if (cmdCheckCols.ExecuteScalar() != null)
                    result = true;
            }
            return result;
        }

        private bool TableContainsColumn(string connectionString, string tableName, string columnName)
        {
            bool result = false;
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT C.COLUMN_NAME
                                                        FROM INFORMATION_SCHEMA.COLUMNS AS C
                                                        WHERE C.TABLE_NAME = '{0}' AND C.TABLE_SCHEMA='dbo' AND C.TABLE_CATALOG = '{2}'
                                                        AND C.COLUMN_NAME = '{1}'", tableName, columnName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                if (cmdCheckCols.ExecuteScalar() != null)
                    result = true;
            }
            return result;
        }

        private bool TableContainsConstraint(string connectionString, string tableName, string constraintName)
        {
            bool result = false;
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT TC.CONSTRAINT_NAME
                                                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as TC
                                                        WHERE TC.TABLE_NAME = '{0}' AND TC.TABLE_SCHEMA='dbo' AND TC.TABLE_CATALOG = '{2}'
                                                        AND TC.CONSTRAINT_NAME = '{1}'", tableName, constraintName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                if (cmdCheckCols.ExecuteScalar() != null)
                    result = true;
            }
            return result;
        }

        public bool TableContainsConstraint(string connectionString, string tableName, string constraintName, IEnumerable<IFieldDescriptor> fields)
        {
            List<IFieldDescriptor> storedFields = new List<IFieldDescriptor>();
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);

            if (!TableContainsConstraint(connectionString, tableName, constraintName))
                return false;

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT CCU.COLUMN_NAME, C.DATA_TYPE, C.IS_NULLABLE 
                                                    FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU
													INNER JOIN INFORMATION_SCHEMA.COLUMNS AS C
														ON CCU.TABLE_CATALOG = C.TABLE_CATALOG AND CCU.TABLE_SCHEMA = C.TABLE_SCHEMA AND CCU.TABLE_NAME = C.TABLE_NAME
                                                    WHERE CCU.TABLE_NAME = '{0}' AND CCU.TABLE_SCHEMA='dbo' AND CCU.TABLE_CATALOG = '{2}'
                                                    AND CCU.CONSTRAINT_NAME = '{1}'", tableName, constraintName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                SqlDataReader reader = cmdCheckCols.ExecuteReader();
                string rdbmsType;
                string colName;
                bool isNullable;
                while (reader.Read())
                {
                    colName = reader["COLUMN_NAME"] is DBNull ? String.Empty : (string)reader["COLUMN_NAME"];
                    rdbmsType = reader["DATA_TYPE"] is DBNull ? String.Empty : (string)reader["DATA_TYPE"];
                    isNullable = reader["IS_NULLABLE"] is DBNull ? true : (String.Compare((string)reader["IS_NULLABLE"], "YES", StringComparison.OrdinalIgnoreCase) == 0 ? true : false);

                    storedFields.Add(new FieldDescriptor(Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(rdbmsType, isNullable), colName));
                }
            }

            int passedNumFD = fields.Count<IFieldDescriptor>();
            int storedNumFD = storedFields.Count;
            if (passedNumFD != storedNumFD)
                return false;

            IEnumerator<IFieldDescriptor> passedFDs = fields.OrderBy<IFieldDescriptor, string>(fd => fd.FieldName).GetEnumerator();
            IEnumerator<IFieldDescriptor> storedFDs = storedFields.OrderBy<IFieldDescriptor, string>(fd => fd.FieldName).GetEnumerator();
            while (passedFDs.MoveNext() && passedFDs.MoveNext())
            {
                FieldDescriptor passedFD = (FieldDescriptor)passedFDs.Current;
                FieldDescriptor storedFD = (FieldDescriptor)storedFDs.Current;
                if (passedFD != storedFD) return false;
            }

            return true;
        }

        private void CreateTable(string connectionString, string tableName, IFieldDescriptor fieldDescriptor)
        {
            //couldn't separate creation table process and creation of at least one column just because MSSQL do not allow to create tables without columns
            string fieldName = fieldDescriptor.FieldName;
            string dbmsTypeName = Types.GetInstance(DBMSTypes.MSSQL).CLRType2DBTypeName(fieldDescriptor.FieldType);

            if (CheckTable(connectionString, tableName) == false)
            {
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    string strCreate = String.Format(@"CREATE TABLE [dbo].{0}(
	                                                            {1} {2} NOT NULL
                                                            )", Enquote(tableName), Enquote(fieldName), dbmsTypeName);
                    SqlCommand cmdCreate = new SqlCommand(strCreate, con);
                    cmdCreate.ExecuteNonQuery();
                }
            }

        }

        public void CreateTable(string connectionString, IRowDescriptor structure)
        {
            //AddColumn, AddForeignConstraint methods are used here 
            //they are used here to decompouse the logic and can be called from the layer above to fire this logic separetly

            //create table and primary key
            //couldn't separate creation table process and creation of primary key just because MSSQL do not allow to create tables without columns
            IPrimaryKeyDescriptor pkd = (IPrimaryKeyDescriptor)structure.RelationDescriptors.First<IRelationDescriptor>(r => r.RelationType == RelationType.PrimaryKey);
            AddPrimaryKeyConstraint(connectionString, pkd, !CheckTable(connectionString, pkd.TableName));

            //add columns
            foreach (IFieldDescriptor fieldDesc in structure.FieldDescriptors)
                AddColumn(connectionString, pkd.TableName, fieldDesc);

            //create parent and foreign keys
            IEnumerable<IRelationDescriptor> rds = structure.RelationDescriptors.Where<IRelationDescriptor>(r => r.RelationType != RelationType.PrimaryKey);
            foreach (IForeignKeyDescriptor rd in rds.ToList<IRelationDescriptor>())
                AddForeignKeyConstraint(connectionString, rd);
        }

        public IRowDescriptor SelectTableStructure(string connectionString, string tableName)
        {
            if (CheckTable(connectionString, tableName) == false)
                return null;

            List<IFieldDescriptor> fieldDescs = CollectFields(connectionString, tableName);
            List<IRelationDescriptor> relationFKDescs = CollectFKRelations(connectionString, tableName);
            List<IRelationDescriptor> relationPKDescs = CollectPKRelations(connectionString, tableName);
            return new RowDescriptor(tableName, fieldDescs, relationFKDescs.Union<IRelationDescriptor>(relationPKDescs).ToList<IRelationDescriptor>());
        }

        private List<IRelationDescriptor> CollectPKRelations(string connectionString, string tableName)
        {
            List<IRelationDescriptor> result = new List<IRelationDescriptor>();
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                //this query os better to put in stored procedure to make the method more readable
                string strCheckCols = String.Format(@"SELECT tc.CONSTRAINT_NAME, ccu.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, c.ORDINAL_POSITION
                                                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
                                                        JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE as ccu
	                                                        ON tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
                                                        JOIN INFORMATION_SCHEMA.COLUMNS as c
	                                                        ON c.TABLE_NAME = ccu.TABLE_NAME
	                                                        AND c.TABLE_SCHEMA = ccu.TABLE_SCHEMA
															AND c.TABLE_CATALOG = ccu.TABLE_CATALOG
	                                                        AND c.COLUMN_NAME = ccu.COLUMN_NAME
                                                        WHERE tc.TABLE_NAME = '{0}' AND tc.TABLE_SCHEMA='dbo' AND tc.TABLE_CATALOG = '{1}'
                                                        AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
                                                        ORDER BY tc.CONSTRAINT_NAME, c.ORDINAL_POSITION", tableName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                SqlDataReader reader = cmdCheckCols.ExecuteReader();

                string constraintName = String.Empty;
                //PrimaryKeyDescriptor? rd = null;//PrimaryKeyDescriptor.Empty;
                PrimaryKeyDescriptor rd = null;

                while (reader.Read())
                {
                    string columnName = reader["COLUMN_NAME"] is DBNull ? String.Empty : (string)reader["COLUMN_NAME"];
                    string dbmsTypeName = reader["DATA_TYPE"] is DBNull ? String.Empty : (string)reader["DATA_TYPE"];
                    bool isNullable = reader["IS_NULLABLE"] is DBNull ? true : ((string)reader["IS_NULLABLE"] == "YES" ? true : false);
                    Type clrType = Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(dbmsTypeName, isNullable);
                    IFieldDescriptor fd = new FieldDescriptor(clrType, columnName);

                    if (constraintName != (string)reader["CONSTRAINT_NAME"])
                    {
                        constraintName = (string)reader["CONSTRAINT_NAME"];
                        if (rd != null) result.Add(rd);
                        IDictionary<int, IFieldDescriptor> fds = new Dictionary<int, IFieldDescriptor>();
                        fds.Add(0, fd);
                        rd = new PrimaryKeyDescriptor(tableName, fds, constraintName);
                    }
                    else
                    {
                        rd = AuxRelation.AddFieldDescInRelationDesc((PrimaryKeyDescriptor)rd, fd);
                    }
                }
                if (rd != null) result.Add(rd);
                //if (rd != PrimaryKeyDescriptor.Empty) result.Add(rd);
            }
            return result;
        }

        private List<IRelationDescriptor> CollectFKRelations(string connectionString, string tableName)
        {
            List<IRelationDescriptor> result = new List<IRelationDescriptor>();
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                //this query os better to put in stored procedure to make the method more readable
                string strCheckCols = String.Format(@"SELECT  
                                                         KCU1.CONSTRAINT_NAME AS FK_CONSTRAINT_NAME 
                                                        ,KCU1.TABLE_NAME AS FK_TABLE_NAME 
                                                        ,KCU1.COLUMN_NAME AS FK_COLUMN_NAME 
	                                                    ,C1.DATA_TYPE AS FK_COLUMN_DATA_TYPE
	                                                    ,C1.IS_NULLABLE AS FK_COLUMN_IS_NULLABLE
                                                        ,KCU1.ORDINAL_POSITION AS FK_ORDINAL_POSITION 
                                                        ,KCU2.CONSTRAINT_NAME AS REFERENCED_CONSTRAINT_NAME 
                                                        ,KCU2.TABLE_NAME AS REFERENCED_TABLE_NAME 
                                                        ,KCU2.COLUMN_NAME AS REFERENCED_COLUMN_NAME 
	                                                    ,C2.DATA_TYPE AS REFERENCED_COLUMN_DATA_TYPE
	                                                    ,C2.IS_NULLABLE AS REFERENCED_COLUMN_IS_NULLABLE
                                                        ,KCU2.ORDINAL_POSITION AS REFERENCED_ORDINAL_POSITION 
                                                    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
                                                    INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC 
                                                        ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG  
                                                        AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
                                                        AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
                                                    INNER JOIN INFORMATION_SCHEMA.COLUMNS as C1
	                                                    ON C1.TABLE_NAME = KCU1.TABLE_NAME
	                                                    AND C1.TABLE_SCHEMA = KCU1.TABLE_SCHEMA
	                                                    AND C1.COLUMN_NAME = KCU1.COLUMN_NAME
                                                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2 
                                                        ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG  
                                                        AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA 
                                                        AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME 
                                                        AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION 
                                                    INNER JOIN INFORMATION_SCHEMA.COLUMNS as C2
	                                                    ON C2.TABLE_NAME = KCU2.TABLE_NAME
	                                                    AND C2.TABLE_SCHEMA = KCU2.TABLE_SCHEMA
	                                                    AND C2.COLUMN_NAME = KCU2.COLUMN_NAME
                                                    WHERE KCU1.TABLE_NAME = '{0}' AND KCU1.TABLE_SCHEMA='dbo' AND KCU1.TABLE_CATALOG = '{1}'
                                                    ORDER BY FK_CONSTRAINT_NAME, FK_ORDINAL_POSITION", tableName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                SqlDataReader reader = cmdCheckCols.ExecuteReader();

                string constraintName = String.Empty;
                //ForeignKeyDescriptor? rd = null; // ForeignKeyDescriptor.Empty;
                ForeignKeyDescriptor rd = null; // ForeignKeyDescriptor.Empty;

                while (reader.Read())
                {
                    string columnName = reader["FK_COLUMN_NAME"] is DBNull ? String.Empty : (string)reader["FK_COLUMN_NAME"];
                    string dbmsTypeName = reader["FK_COLUMN_DATA_TYPE"] is DBNull ? String.Empty : (string)reader["FK_COLUMN_DATA_TYPE"];
                    bool isNullable = reader["FK_COLUMN_IS_NULLABLE"] is DBNull ? true : ((string)reader["FK_COLUMN_IS_NULLABLE"] == "YES" ? true : false);
                    Type clrType = Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(dbmsTypeName, isNullable);
                    IFieldDescriptor fd = new FieldDescriptor(clrType, columnName);

                    string relColumnName = reader["REFERENCED_COLUMN_NAME"] is DBNull ? String.Empty : (string)reader["REFERENCED_COLUMN_NAME"];
                    string relDbmsTypeName = reader["REFERENCED_COLUMN_DATA_TYPE"] is DBNull ? String.Empty : (string)reader["REFERENCED_COLUMN_DATA_TYPE"];
                    bool relIsNullable = reader["REFERENCED_COLUMN_IS_NULLABLE"] is DBNull ? true : ((string)reader["REFERENCED_COLUMN_IS_NULLABLE"] == "YES" ? true : false);
                    Type relClrType = Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(relDbmsTypeName, relIsNullable);
                    IFieldDescriptor relfd = new FieldDescriptor(relClrType, relColumnName);

                    string relTableName = reader["REFERENCED_TABLE_NAME"] is DBNull ? String.Empty : (string)reader["REFERENCED_TABLE_NAME"];

                    if (constraintName != (string)reader["FK_CONSTRAINT_NAME"])
                    {
                        constraintName = (string)reader["FK_CONSTRAINT_NAME"];
                        if (rd != null) result.Add(rd);
                        IDictionary<int, IFieldDescriptor> fds = new Dictionary<int, IFieldDescriptor>();
                        fds.Add(0, fd);
                        IDictionary<int, IFieldDescriptor> rfds = new Dictionary<int, IFieldDescriptor>();
                        rfds.Add(0, relfd);
                        rd = new ForeignKeyDescriptor(tableName, fds, relTableName, rfds, constraintName);
                    }
                    else
                    {
                        rd = AuxRelation.AddFieldDescInRelationDesc((ForeignKeyDescriptor)rd, fd, relfd);
                    }
                }
                if (rd != null) result.Add(rd);
            }
            return result;
        }

        private List<IFieldDescriptor> CollectFields(string connectionString, string tableName)
        {
            List<IFieldDescriptor> result = new List<IFieldDescriptor>();
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT C.COLUMN_NAME, C.DATA_TYPE, C.IS_NULLABLE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
                                                        FROM INFORMATION_SCHEMA.COLUMNS AS C
                                                        WHERE C.TABLE_NAME = '{0}' AND C.TABLE_SCHEMA='dbo' AND C.TABLE_CATALOG = '{1}'
                                                        AND NOT EXISTS (SELECT 1
				                                                        FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CU
				                                                        WHERE CU.COLUMN_NAME = C.COLUMN_NAME
				                                                        AND CU.TABLE_CATALOG = C.TABLE_CATALOG
				                                                        AND CU.TABLE_SCHEMA = C.TABLE_SCHEMA
				                                                        AND CU.TABLE_NAME = C.TABLE_NAME)", tableName, csb.InitialCatalog);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                SqlDataReader reader = cmdCheckCols.ExecuteReader();
                while (reader.Read())
                {
                    string dbTypeName = reader["DATA_TYPE"] is DBNull ? String.Empty : (string)reader["DATA_TYPE"];
                    string fieldName = reader["COLUMN_NAME"] is DBNull ? String.Empty : (string)reader["COLUMN_NAME"];
                    bool isNullable = reader["IS_NULLABLE"] is DBNull ? true : ((string)reader["IS_NULLABLE"] == "YES" ? true : false);
                    if (dbTypeName == "nvarchar")
                    {
                        object[] attrs = new object[1] { reader["CHARACTER_MAXIMUM_LENGTH"] is DBNull ? DEFAULT_CHARACTER_MAXIMUM_LENGTH : (int)reader["CHARACTER_MAXIMUM_LENGTH"] };
                        result.Add(new FieldDescriptor(Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(dbTypeName, isNullable), fieldName, attrs));
                    }
                    else if (dbTypeName == "numeric")
                    {
                        object[] attrs = new object[2] 
                                            { 
                                                reader["NUMERIC_PRECISION"] is DBNull ? DEFAULT_NUMERIC_PRECISION : (int)(byte)reader["NUMERIC_PRECISION"],
                                                reader["NUMERIC_SCALE"] is DBNull ? DEFAULT_NUMERIC_SCALE : (int)reader["NUMERIC_SCALE"]
                                            };
                        result.Add(new FieldDescriptor(Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(dbTypeName, isNullable), fieldName, attrs));
                    }
                    else
                        result.Add(new FieldDescriptor(Types.GetInstance(DBMSTypes.MSSQL).DBTypeName2CLRType(dbTypeName, isNullable), fieldName));
                }
            }
            return result;
        }

        public void AddColumn(string connectionString, string tableName, IFieldDescriptor fieldDesc)
        {
            if (CheckTable(connectionString, tableName) == true)
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    //check for existence of column is omited

                    string clrTypeName = Nullable.GetUnderlyingType(fieldDesc.FieldType) == null ? fieldDesc.FieldType.Name : Nullable.GetUnderlyingType(fieldDesc.FieldType).Name;
                    string dbmsTypeName = Types.GetInstance(DBMSTypes.MSSQL).CLRTypeName2DBTypeName(clrTypeName);

                    if (dbmsTypeName == "nvarchar")
                    {
                        if (fieldDesc.Attributes.Count() == 1)
                            dbmsTypeName += String.Format("({0})", fieldDesc.Attributes[0]);
                        else
                            dbmsTypeName += String.Format("({0})", DEFAULT_CHARACTER_MAXIMUM_LENGTH);
                    }
                    if (dbmsTypeName == "numeric")
                    {
                        if (fieldDesc.Attributes.Count() == 2)
                            dbmsTypeName += String.Format("({0}, {1})", fieldDesc.Attributes[0], fieldDesc.Attributes[1]);
                        else
                            dbmsTypeName += String.Format("({0}, {1})", DEFAULT_NUMERIC_PRECISION, DEFAULT_NUMERIC_SCALE);
                    }

                    string nullablestr = Types.IsCLRTypeNullable(fieldDesc.FieldType) ? "NULL" : "NOT NULL";

                    if (TableContainsAnyRecord(connectionString, tableName) && !Types.IsCLRTypeNullable(fieldDesc.FieldType))
                        return;

                    string strCreate = String.Format(@"ALTER TABLE [dbo].{0} ADD
	                                                        {1} {2} {3}", Enquote(tableName), Enquote(fieldDesc.FieldName), dbmsTypeName, nullablestr);
                    SqlCommand cmdCreate = new SqlCommand(strCreate, con);
                    cmdCreate.ExecuteNonQuery();
                }
        }

        public void DropColumn(string connectionString, string tableName, IFieldDescriptor fieldDesc)
        {
            if (CheckTable(connectionString, tableName) == true)
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    //check for existence of column is omited

                    string strCommand = String.Format(@"ALTER TABLE [dbo].{0} DROP COLUMN {1}", Enquote(tableName), Enquote(fieldDesc.FieldName));
                    SqlCommand cmdCreate = new SqlCommand(strCommand, con);
                    cmdCreate.ExecuteNonQuery();
                }
        }

        private bool IsContainColumn(string connectionString, string tableName, IFieldDescriptor fieldDesc)
        {
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCheckCols = String.Format(@"SELECT 1
                                                        FROM INFORMATION_SCHEMA.COLUMNS AS C
                                                        WHERE C.TABLE_NAME = '{0}' AND C.TABLE_SCHEMA='dbo' AND C.TABLE_CATALOG = '{1}'
														AND C.COLUMN_NAME = '{2}'", tableName, csb.InitialCatalog, fieldDesc.FieldName);
                SqlCommand cmdCheckCols = new SqlCommand(strCheckCols, con);
                var res = cmdCheckCols.ExecuteScalar();
                if (res == null)
                    return false;
                else
                    return true;
            }
        }

        public void DropTable(string connectionString, string tableName)
        {
            if (CheckTable(connectionString, tableName) == false)
                return;

            SqlConnection dbmsCon = new SqlConnection(connectionString);
            SqlCommand cmd = null;
            SqlDataReader reader = null;

            //try
            //{
            dbmsCon.Open();
            cmd = new SqlCommand(String.Format("DROP TABLE {0}", Enquote(tableName)), dbmsCon);
            cmd.ExecuteNonQuery();
            //}
            //catch
            //{
            //writing in logs
            //...
            //showing the message
            //}
            //finally
            //{
            if (reader != null) reader.Dispose();
            if (cmd != null) cmd.Dispose();
            if (dbmsCon != null) dbmsCon.Dispose();
            //}

        }

        public void AddForeignKeyConstraint(string connectionString, IForeignKeyDescriptor rd)
        {
            if (rd.RelationType == RelationType.PrimaryKey)
                throw new Exception("Primary key constraint can be added by means of this method. Use AddPrimaryKeyConstraint instead.");

            string tableName = rd.TableName;
            string relTableName = rd.RelatedTableName;

            string fieldNames = String.Empty;
            foreach (KeyValuePair<int, IFieldDescriptor> kv in rd.FieldDescriptors.OrderBy<KeyValuePair<int, IFieldDescriptor>, int>(kv => kv.Key))
            {
                fieldNames = (fieldNames == String.Empty) ? Enquote(kv.Value.FieldName) : fieldNames + ", " + Enquote(kv.Value.FieldName);
                if (!IsContainColumn(connectionString, rd.TableName, kv.Value))
                    AddColumn(connectionString, tableName, kv.Value);
            }

            string relFieldNames = String.Empty;
            foreach (KeyValuePair<int, IFieldDescriptor> rfd in rd.RelatedFieldDescriptors.OrderBy<KeyValuePair<int, IFieldDescriptor>, int>(kv => kv.Key))
                relFieldNames = (relFieldNames == String.Empty) ? Enquote(rfd.Value.FieldName) : relFieldNames + ", " + Enquote(rfd.Value.FieldName);

            if ((CheckTable(connectionString, tableName)) && (CheckTable(connectionString, relTableName)))

                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    string strCreate = String.Format(@"ALTER TABLE [dbo].{0}  WITH CHECK ADD CONSTRAINT {4} FOREIGN KEY({2})
                                                                              REFERENCES [dbo].{1} ({3})", Enquote(tableName), Enquote(relTableName), fieldNames, relFieldNames, Enquote(rd.Name));
                    SqlCommand cmdCreate = new SqlCommand(strCreate, con);
                    cmdCreate.ExecuteNonQuery();
                }
        }

        public void DropForeignKeyConstraint(string connectionString, IRelationDescriptor rd)
        {
            if (CheckTable(connectionString, rd.TableName) == true)
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    con.Open();
                    //check for existence of constraint is omited

                    string strCommand = String.Format(@"ALTER TABLE [dbo].{0} DROP CONSTRAINT {1}", Enquote(rd.TableName), Enquote(rd.Name));
                    SqlCommand cmdCreate = new SqlCommand(strCommand, con);
                    cmdCreate.ExecuteNonQuery();
                }
        }

        public void AddPrimaryKeyConstraint(string connectionString, IPrimaryKeyDescriptor pkd, bool createTable)
        {
            //couldn't separate creation table process and creation of primary key just because MSSQL do not allow to create tables without columns
            string tableName = pkd.TableName;
            string pkFieldNames = String.Empty;

            for (int i = 0; i < pkd.FieldDescriptors.Count; i++)
            {
                IFieldDescriptor fd = pkd.FieldDescriptors[i];
                pkFieldNames = (pkFieldNames == String.Empty) ? Enquote(fd.FieldName) : pkFieldNames + ", " + Enquote(fd.FieldName);

                if (i == 0 && createTable && CheckTable(connectionString, tableName) == false)
                    CreateTable(connectionString, tableName, fd);
                else
                {
                    if (!IsContainColumn(connectionString, pkd.TableName, fd))
                        AddColumn(connectionString, tableName, fd);
                }
            }

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strCreate = String.Format(@"ALTER TABLE [dbo].{0}  WITH CHECK ADD  
                                                CONSTRAINT {2} PRIMARY KEY CLUSTERED 
                                            ({1})
                                            ", Enquote(tableName), pkFieldNames, Enquote(pkd.Name));
                SqlCommand cmdCreate = new SqlCommand(strCreate, con);
                cmdCreate.ExecuteNonQuery();
            }
        }

        public void DropPrimaryKeyConstraint(string connectionString, IPrimaryKeyDescriptor pkd)
        {
            DropForeignKeyConstraint(connectionString, pkd);
        }

        #endregion


        #region DML
        public bool DeleteRow(string connectionString, IPrimaryKey pk)
        {
            bool result = false;

            SqlConnection dbmsCon = new SqlConnection(connectionString);
            SqlCommand cmd = null;
            SqlDataReader reader = null;


            string predicate = String.Empty;
            foreach (IField field in pk.Fields.Select<KeyValuePair<int, IField>, IField>(kv => kv.Value))
            {
                predicate = predicate == String.Empty ? Enquote(field.FieldName) + " = " : predicate + " AND " + Enquote(field.FieldName) + " = ";

                if (field.FieldValue != null)
                {
                    if (field.FieldType == typeof(Int16) || field.FieldType == Types.GetCLRNullableType(typeof(Int16))
                        || field.FieldType == typeof(Int32) || field.FieldType == Types.GetCLRNullableType(typeof(Int32))
                        || field.FieldType == typeof(Int64) || field.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                        predicate += String.Format("{0}", field.FieldValue.ToString());

                    //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                    if (field.FieldType == typeof(double) || field.FieldType == Types.GetCLRNullableType(typeof(double)))
                        predicate += String.Format("{0}", ((double)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Single) || field.FieldType == Types.GetCLRNullableType(typeof(Single)))
                        predicate += String.Format("{0}", ((Single)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Decimal) || field.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                        predicate += String.Format("{0}", ((Decimal)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                    if (field.FieldType == typeof(string)
                        || field.FieldType == typeof(bool) || field.FieldType == Types.GetCLRNullableType(typeof(bool))
                        || field.FieldType == typeof(DateTime) || field.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                        || field.FieldType == typeof(Guid) || field.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                        predicate += String.Format("'{0}'", field.FieldValue.ToString());
                }
                else
                    //we'll got somefield = NULL alternatively we should use somefield IS NULL
                    predicate += "NULL";
            }


            try
            {
                dbmsCon.Open();
                cmd = new SqlCommand(String.Format("DELETE FROM {0} WHERE {1}", Enquote(pk.TableName), predicate), dbmsCon);
                int rowCount = cmd.ExecuteNonQuery();
                if (rowCount > 0) result = true;
            }
            //catch
            //{
            //delete can fail if there are records in DB to which deleting row reference

                //writing in logs
            //...
            //showing the message
            //}
            finally
            {
                if (reader != null) reader.Dispose();
                if (cmd != null) cmd.Dispose();
                if (dbmsCon != null) dbmsCon.Dispose();
            }

            return result;
        }

        public bool DeleteRow(string connectionString, IRow row)
        {
            PrimaryKey pk = (PrimaryKey)row.Relations.FirstOrDefault<IRelation>(r => r.RelationType == RelationType.PrimaryKey);
            return DeleteRow(connectionString, pk);
        }

        public void AddRow(string connectionString, IRow row)
        {
            //check table existence here

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                string tableName = row.Relations.First<IRelation>(r => r.RelationType == RelationType.PrimaryKey).TableName;

                string columns = String.Empty;
                string columnsValues = String.Empty;

                foreach (IField field in row.Fields)
                {
                    columns += Enquote(field.FieldName) + ",";

                    if (field.FieldValue != null)
                    {
                        if (field.FieldType == typeof(Int16) || field.FieldType == Types.GetCLRNullableType(typeof(Int16))
                            || field.FieldType == typeof(Int32) || field.FieldType == Types.GetCLRNullableType(typeof(Int32))
                            || field.FieldType == typeof(Int64) || field.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                            columnsValues += String.Format("{0},", field.FieldValue.ToString());

                        //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                        if (field.FieldType == typeof(double) || field.FieldType == Types.GetCLRNullableType(typeof(double)))
                            columnsValues += String.Format("{0},", ((double)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (field.FieldType == typeof(Single) || field.FieldType == Types.GetCLRNullableType(typeof(Single)))
                            columnsValues += String.Format("{0},", ((Single)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (field.FieldType == typeof(Decimal) || field.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                            columnsValues += String.Format("{0},", ((Decimal)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                        if (field.FieldType == typeof(string)
                            || field.FieldType == typeof(bool) || field.FieldType == Types.GetCLRNullableType(typeof(bool))
                            || field.FieldType == typeof(DateTime) || field.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                            || field.FieldType == typeof(Guid) || field.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                            columnsValues += String.Format("'{0}',", field.FieldValue.ToString());
                    }
                    else
                    {
                        columnsValues += "NULL,";
                    }
                }

                List<IField> processedFields = new List<IField>();
                foreach (IRelation relation in row.Relations)
                    foreach (IField field in relation.Fields.Select<KeyValuePair<int, IField>, IField>(kv => kv.Value))
                    {
                        //because the same field can be in PK and FK it's important to check if the field has been processed already or not
                        if (processedFields.Exists(f => f.Equals(field)))
                            continue;
                        processedFields.Add(field);

                        columns += Enquote(field.FieldName) + ",";

                        if (field.FieldValue != null)
                        {
                            if (field.FieldType == typeof(Int16) || field.FieldType == Types.GetCLRNullableType(typeof(Int16))
                                || field.FieldType == typeof(Int32) || field.FieldType == Types.GetCLRNullableType(typeof(Int32))
                                || field.FieldType == typeof(Int64) || field.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                                columnsValues += String.Format("{0},", field.FieldValue.ToString());

                            //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                            if (field.FieldType == typeof(double) || field.FieldType == Types.GetCLRNullableType(typeof(double)))
                                columnsValues += String.Format("{0},", ((double)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                            if (field.FieldType == typeof(Single) || field.FieldType == Types.GetCLRNullableType(typeof(Single)))
                                columnsValues += String.Format("{0},", ((Single)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                            if (field.FieldType == typeof(Decimal) || field.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                                columnsValues += String.Format("{0},", ((Decimal)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                            if (field.FieldType == typeof(string)
                                || field.FieldType == typeof(bool) || field.FieldType == Types.GetCLRNullableType(typeof(bool))
                                || field.FieldType == typeof(DateTime) || field.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                                || field.FieldType == typeof(Guid) || field.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                                columnsValues += String.Format("'{0}',", field.FieldValue.ToString());
                        }
                        else
                        {
                            columnsValues += "NULL,";
                        }
                    }

                char[] charsToTrim = { ',', ' ' };
                columns = columns.TrimEnd(charsToTrim);
                columnsValues = columnsValues.TrimEnd(charsToTrim);

                string strAttrInsert =
                    String.Format("Insert Into {0} ({1}) values ({2})", Enquote(tableName), columns, columnsValues);
                SqlCommand cmdAttrInsert = new SqlCommand(strAttrInsert, con);
                cmdAttrInsert.ExecuteNonQuery();
            }
        }

        public void UpdateRow(string connectionString, IRow row)
        {
            PrimaryKey pk = (PrimaryKey)row.Relations.First<IRelation>(r => r.RelationType == RelationType.PrimaryKey);
            string tableName = Unquote(pk.TableName);

            string predicate = String.Empty;
            foreach (IField field in pk.Fields.Select<KeyValuePair<int, IField>, IField>(kv => kv.Value))
            {
                predicate = predicate == String.Empty ? Enquote(field.FieldName) + " = " : predicate + " AND " + Enquote(field.FieldName) + " = ";

                if (field.FieldValue != null)
                {
                    if (field.FieldType == typeof(Int16) || field.FieldType == Types.GetCLRNullableType(typeof(Int16))
                        || field.FieldType == typeof(Int32) || field.FieldType == Types.GetCLRNullableType(typeof(Int32))
                        || field.FieldType == typeof(Int64) || field.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                        predicate += String.Format("{0}", field.FieldValue.ToString());

                    //to avoid commas as decimal point (which leaeds to Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES.)
                    if (field.FieldType == typeof(double) || field.FieldType == Types.GetCLRNullableType(typeof(double)))
                        predicate += String.Format("{0}", ((double)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Single) || field.FieldType == Types.GetCLRNullableType(typeof(Single)))
                        predicate += String.Format("{0}", ((Single)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Decimal) || field.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                        predicate += String.Format("{0}", ((Decimal)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                    if (field.FieldType == typeof(string)
                        || field.FieldType == typeof(bool) || field.FieldType == Types.GetCLRNullableType(typeof(bool))
                        || field.FieldType == typeof(DateTime) || field.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                        || field.FieldType == typeof(Guid) || field.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                        predicate += String.Format("'{0}'", field.FieldValue.ToString());
                }
                else
                    //we'll got somefield = NULL alternatively we should use somefield IS NULL
                    predicate += "NULL";
            }

            List<IField> processedFields = new List<IField>();

            string columnsAndValues = String.Empty;
            //note the order of descriptors
            foreach (IRelation r in row.Relations.Where<IRelation>(r => r.RelationType != RelationType.PrimaryKey).ToList<IRelation>())
                foreach (IField f in r.Fields.Select<KeyValuePair<int, IField>, IField>(kv => kv.Value))
                {
                    //because the same field can be in PK and FK it's important to check if the field has been processed already or not
                    if (processedFields.Exists(f1 => f1.Equals(f)))
                        continue;
                    processedFields.Add(f);

                    columnsAndValues = (columnsAndValues == String.Empty) ? Enquote(f.FieldName) : columnsAndValues + ", " + Enquote(f.FieldName);

                    if (f.FieldValue != null)
                    {
                        if (f.FieldType == typeof(Int16) || f.FieldType == Types.GetCLRNullableType(typeof(Int16))
                            || f.FieldType == typeof(Int32) || f.FieldType == Types.GetCLRNullableType(typeof(Int32))
                            || f.FieldType == typeof(Int64) || f.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                            columnsAndValues += String.Format(" = {0}", f.FieldValue.ToString());

                        //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                        if (f.FieldType == typeof(double) || f.FieldType == Types.GetCLRNullableType(typeof(double)))
                            columnsAndValues += String.Format(" = {0}", ((double)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (f.FieldType == typeof(Single) || f.FieldType == Types.GetCLRNullableType(typeof(Single)))
                            columnsAndValues += String.Format(" = {0}", ((Single)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (f.FieldType == typeof(Decimal) || f.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                            columnsAndValues += String.Format(" = {0}", ((Decimal)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                        if (f.FieldType == typeof(string)
                            || f.FieldType == typeof(bool) || f.FieldType == Types.GetCLRNullableType(typeof(bool))
                            || f.FieldType == typeof(DateTime) || f.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                            || f.FieldType == typeof(Guid) || f.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                            columnsAndValues += String.Format(" = '{0}'", f.FieldValue.ToString());
                    }
                    else
                        columnsAndValues += " = NULL";
                }

            foreach (IField f in row.Fields)
            {
                columnsAndValues = (columnsAndValues == String.Empty) ? Enquote(f.FieldName) : columnsAndValues + ", " + Enquote(f.FieldName);


                if (f.FieldValue != null)
                {
                    if (f.FieldType == typeof(Int16) || f.FieldType == Types.GetCLRNullableType(typeof(Int16))
                        || f.FieldType == typeof(Int32) || f.FieldType == Types.GetCLRNullableType(typeof(Int32))
                        || f.FieldType == typeof(Int64) || f.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                        columnsAndValues += String.Format(" = {0}", f.FieldValue.ToString());

                    //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                    if (f.FieldType == typeof(double) || f.FieldType == Types.GetCLRNullableType(typeof(double)))
                        columnsAndValues += String.Format(" = {0}", ((double)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (f.FieldType == typeof(Single) || f.FieldType == Types.GetCLRNullableType(typeof(Single)))
                        columnsAndValues += String.Format(" = {0}", ((Single)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (f.FieldType == typeof(Decimal) || f.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                        columnsAndValues += String.Format(" = {0}", ((Decimal)f.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                    if (f.FieldType == typeof(string)
                        || f.FieldType == typeof(bool) || f.FieldType == Types.GetCLRNullableType(typeof(bool))
                        || f.FieldType == typeof(DateTime) || f.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                        || f.FieldType == typeof(Guid) || f.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                        columnsAndValues += String.Format(" = '{0}'", f.FieldValue.ToString());
                }
                else
                    columnsAndValues += " = NULL";
            }


            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();

                string strUpdate =
                    String.Format(@"UPDATE [dbo].{0}
                                    SET {1}
                                    WHERE {2}", Enquote(tableName), columnsAndValues, predicate);
                SqlCommand cmdAttrInsert = new SqlCommand(strUpdate, con);
                cmdAttrInsert.ExecuteNonQuery();
            }
        }

        public IRow SelectRow(string connectionString, IPrimaryKey pk)
        {
            IRow result = null;

            IRowDescriptor structure = SelectTableStructure(connectionString, pk.TableName);
            if (structure == null) return result;

            string predicate = String.Empty;
            foreach (IField field in pk.Fields.Select<KeyValuePair<int, IField>, IField>(kv => kv.Value))
            {
                predicate = predicate == String.Empty ? Enquote(field.FieldName) + " = " : predicate + " AND " + Enquote(field.FieldName) + " = ";

                if (field.FieldValue != null)
                {
                    if (field.FieldType == typeof(Int16) || field.FieldType == Types.GetCLRNullableType(typeof(Int16))
                        || field.FieldType == typeof(Int32) || field.FieldType == Types.GetCLRNullableType(typeof(Int32))
                        || field.FieldType == typeof(Int64) || field.FieldType == Types.GetCLRNullableType(typeof(Int64)))
                        predicate += String.Format("{0}", field.FieldValue.ToString());

                    //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                    if (field.FieldType == typeof(double) || field.FieldType == Types.GetCLRNullableType(typeof(double)))
                        predicate += String.Format("{0}", ((double)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Single) || field.FieldType == Types.GetCLRNullableType(typeof(Single)))
                        predicate += String.Format("{0}", ((Single)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                    if (field.FieldType == typeof(Decimal) || field.FieldType == Types.GetCLRNullableType(typeof(Decimal)))
                        predicate += String.Format("{0}", ((Decimal)field.FieldValue).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                    if (field.FieldType == typeof(string)
                        || field.FieldType == typeof(bool) || field.FieldType == Types.GetCLRNullableType(typeof(bool))
                        || field.FieldType == typeof(DateTime) || field.FieldType == Types.GetCLRNullableType(typeof(DateTime))
                        || field.FieldType == typeof(Guid) || field.FieldType == Types.GetCLRNullableType(typeof(Guid)))
                        predicate += String.Format("'{0}'", field.FieldValue.ToString());
                }
                else
                    //we'll got somefield = NULL alternatively we should use somefield IS NULL
                    predicate += "NULL";
            }

            string columnsList = String.Empty;
            //note the order of descriptors
            foreach (IRelationDescriptor r in structure.RelationDescriptors)
                foreach (IFieldDescriptor f in r.FieldDescriptors.OrderBy<KeyValuePair<int, IFieldDescriptor>, int>(kv => kv.Key).Select<KeyValuePair<int, IFieldDescriptor>, IFieldDescriptor>(kv => kv.Value))
                {
                    columnsList = (columnsList == String.Empty) ? Enquote(f.FieldName) : columnsList + ", " + Enquote(f.FieldName);
                }
            foreach (IFieldDescriptor f in structure.FieldDescriptors)
            {
                columnsList = columnsList + ", " + Enquote(f.FieldName);
            }

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strSelect = String.Format("SELECT {2} FROM {0} WHERE {1}", Enquote(pk.TableName), predicate, columnsList);
                SqlCommand cmdSelect = new SqlCommand(strSelect, con);
                SqlDataReader reader = cmdSelect.ExecuteReader();

                List<IField> fields = new List<IField>();
                List<IRelation> relations = new List<IRelation>();

                if (reader.Read())
                {
                    fields.Clear();
                    relations.Clear();

                    //in the same order we'll collect the values now
                    foreach (IRelationDescriptor r in structure.RelationDescriptors)
                    {
                        Dictionary<int, IField> selectedFields = new Dictionary<int, IField>();
                        Dictionary<int, IField> relSelectedFields = new Dictionary<int, IField>();

                        if (r.RelationType == RelationType.PrimaryKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                            }
                            relations.Add(new PrimaryKey(r.TableName, selectedFields, r.Name));
                        }
                        else if (r.RelationType == RelationType.ForeignKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                IFieldDescriptor rfd = ((IForeignKeyDescriptor)r).RelatedFieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                                relSelectedFields.Add(i, new Field(rfd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName])); //related field may differ in its name but will have equal value, so we reconstruct it based on its descriptor and selected values
                            }
                            relations.Add(new ForeignKey(r.TableName, selectedFields, ((IForeignKeyDescriptor)r).RelatedTableName, relSelectedFields, r.Name));
                        }
                    }
                    foreach (IFieldDescriptor fd in structure.FieldDescriptors)
                    {
                        fields.Add(new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                    }
                    result = new Row(fields, relations);
                }
            }
            return result;
        }

        public IEnumerable<IRow> SelectRows(string connectionString, string tableName)
        {
            List<IRow> result = new List<IRow>();

            IRowDescriptor structure = SelectTableStructure(connectionString, tableName);
            if (structure == null) return null;

            string columnsList = String.Empty;
            //note the order of descriptors
            foreach (IRelationDescriptor r in structure.RelationDescriptors)
                foreach (IFieldDescriptor f in r.FieldDescriptors.OrderBy<KeyValuePair<int, IFieldDescriptor>, int>(kv => kv.Key).Select<KeyValuePair<int, IFieldDescriptor>, IFieldDescriptor>(kv => kv.Value))
                    columnsList = (columnsList == String.Empty) ? Enquote(f.FieldName) : columnsList + ", " + Enquote(f.FieldName);
            foreach (IFieldDescriptor f in structure.FieldDescriptors)
                columnsList = columnsList + ", " + Enquote(f.FieldName);

            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strSelect = String.Format("SELECT {1} FROM {0}", Enquote(tableName), columnsList);
                SqlCommand cmdSelect = new SqlCommand(strSelect, con);
                SqlDataReader reader = cmdSelect.ExecuteReader();

                List<IField> fields = new List<IField>();
                List<IRelation> relations = new List<IRelation>();

                while (reader.Read())
                {
                    fields.Clear();
                    relations.Clear();

                    //in the same order we'll collect the values now
                    foreach (IRelationDescriptor r in structure.RelationDescriptors)
                    {
                        Dictionary<int, IField> selectedFields = new Dictionary<int, IField>();
                        Dictionary<int, IField> relSelectedFields = new Dictionary<int, IField>();

                        if (r.RelationType == RelationType.PrimaryKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                            }
                            relations.Add(new PrimaryKey(r.TableName, selectedFields, r.Name));
                        }
                        else if (r.RelationType == RelationType.ForeignKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                IFieldDescriptor rfd = ((IForeignKeyDescriptor)r).RelatedFieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                                relSelectedFields.Add(i, new Field(rfd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName])); //related field may differ in its name but will have equal value, so we reconstruct it based on its descriptor and selected values
                            }
                            relations.Add(new ForeignKey(r.TableName, selectedFields, ((IForeignKeyDescriptor)r).RelatedTableName, relSelectedFields, r.Name));
                        }
                    }
                    foreach (IFieldDescriptor fd in structure.FieldDescriptors)
                    {
                        fields.Add(new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                    }
                    result.Add(new Row(fields, relations));
                }
            }
            return result;
        }

        public IEnumerable<IRow> SelectRows(string connectionString, string tableName, IEnumerable<IORMComparison> predicate)
        {
            List<IRow> result = new List<IRow>();

            IRowDescriptor structure = SelectTableStructure(connectionString, tableName);
            if (structure == null) return null;

            string columnsList = String.Empty;
            //note the order of descriptors
            foreach (IRelationDescriptor r in structure.RelationDescriptors)
                foreach (IFieldDescriptor f in r.FieldDescriptors.OrderBy<KeyValuePair<int, IFieldDescriptor>, int>(kv => kv.Key).Select<KeyValuePair<int, IFieldDescriptor>, IFieldDescriptor>(kv => kv.Value))
                    columnsList = (columnsList == String.Empty) ? Enquote(f.FieldName) : columnsList + ", " + Enquote(f.FieldName);
            foreach (IFieldDescriptor f in structure.FieldDescriptors)
                columnsList = columnsList + ", " + Enquote(f.FieldName);

            string predicateString = String.Empty;
            if (predicate.Count() > 0)
                foreach (IORMComparison c in predicate)
                {
                    c.CheckCorrespondenceToRowDescriptor(structure);

                    string relOperator;
                    switch (c.Condition)
                    {
                        case ORMComparisonOperator.Equal:
                            {
                                if (c.Value == null)
                                    relOperator = " IS ";
                                else
                                    relOperator = " = ";
                                break;
                            }
                        case ORMComparisonOperator.Greater:
                            {
                                relOperator = " > ";
                                break;
                            }
                        case ORMComparisonOperator.GreaterOrEqual:
                            {
                                relOperator = " >= ";
                                break;
                            }
                        case ORMComparisonOperator.In:
                            {
                                relOperator = " IN ";
                                break;
                            }
                        case ORMComparisonOperator.Less:
                            {
                                relOperator = " < ";
                                break;
                            }
                        case ORMComparisonOperator.LessOrEqual:
                            {
                                relOperator = " <= ";
                                break;
                            }
                        case ORMComparisonOperator.Like:
                            {
                                relOperator = " LIKE ";
                                break;
                            }
                        case ORMComparisonOperator.NotEqual:
                            {
                                if (c.Value == null)
                                    relOperator = " IS NOT ";
                                else
                                    relOperator = " <> ";
                                break;
                            }
                        default:
                            {
                                relOperator = " = ";
                                break;
                            }
                    }


                    predicateString = predicateString == String.Empty ? Enquote(c.FieldName) + relOperator : predicateString + " AND " + Enquote(c.FieldName) + relOperator;

                    if (c.Value != null)
                    {
                        Type valueType = c.Value.GetType();
                        if (valueType == typeof(Int16) || valueType == Types.GetCLRNullableType(typeof(Int16))
                            || valueType == typeof(Int32) || valueType == Types.GetCLRNullableType(typeof(Int32))
                            || valueType == typeof(Int64) || valueType == Types.GetCLRNullableType(typeof(Int64)))
                            predicateString += String.Format("{0}", c.Value.ToString());

                        //to avoid commas as decimal point (which leads to "Число столбцов в инструкции INSERT меньше числа значений, указанных в предложении VALUES" exception.)
                        if (valueType == typeof(double) || valueType == Types.GetCLRNullableType(typeof(double)))
                            predicateString += String.Format("{0}", ((double)c.Value).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (valueType == typeof(Single) || valueType == Types.GetCLRNullableType(typeof(Single)))
                            predicateString += String.Format("{0}", ((Single)c.Value).ToString(CultureInfo.CreateSpecificCulture("en-GB")));
                        if (valueType == typeof(Decimal) || valueType == Types.GetCLRNullableType(typeof(Decimal)))
                            predicateString += String.Format("{0}", ((Decimal)c.Value).ToString(CultureInfo.CreateSpecificCulture("en-GB")));

                        if (valueType == typeof(string)
                            || valueType == typeof(bool) || valueType == Types.GetCLRNullableType(typeof(bool))
                            || valueType == typeof(DateTime) || valueType == Types.GetCLRNullableType(typeof(DateTime))
                            || valueType == typeof(Guid) || valueType == Types.GetCLRNullableType(typeof(Guid)))
                            predicateString += String.Format("'{0}'", c.Value.ToString());
                    }
                    else
                        //we'll got somefield = NULL alternatively we should use somefield IS NULL
                        predicateString += "NULL";
                }


            using (SqlConnection con = new SqlConnection(connectionString))
            {
                con.Open();
                string strSelect;
                if (predicate.Count() > 0)
                    strSelect = String.Format("SELECT {1} FROM {0} WHERE {2}", Enquote(tableName), columnsList, predicateString);
                else
                    strSelect = String.Format("SELECT {1} FROM {0}", Enquote(tableName), columnsList);
                SqlCommand cmdSelect = new SqlCommand(strSelect, con);
                SqlDataReader reader = cmdSelect.ExecuteReader();

                List<IField> fields = new List<IField>();
                List<IRelation> relations = new List<IRelation>();

                while (reader.Read())
                {
                    fields.Clear();
                    relations.Clear();

                    //in the same order we'll collect the values now
                    foreach (IRelationDescriptor r in structure.RelationDescriptors)
                    {
                        Dictionary<int, IField> selectedFields = new Dictionary<int, IField>();
                        Dictionary<int, IField> relSelectedFields = new Dictionary<int, IField>();

                        if (r.RelationType == RelationType.PrimaryKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                            }
                            relations.Add(new PrimaryKey(r.TableName, selectedFields, r.Name));
                        }
                        else if (r.RelationType == RelationType.ForeignKey)
                        {
                            for (int i = 0; i < r.FieldDescriptors.Count; i++)
                            {
                                IFieldDescriptor fd = r.FieldDescriptors[i];
                                IFieldDescriptor rfd = ((IForeignKeyDescriptor)r).RelatedFieldDescriptors[i];
                                selectedFields.Add(i, new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                                relSelectedFields.Add(i, new Field(rfd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName])); //related field may differ in its name but will have equal value, so we reconstruct it based on its descriptor and selected values
                            }
                            relations.Add(new ForeignKey(r.TableName, selectedFields, ((IForeignKeyDescriptor)r).RelatedTableName, relSelectedFields, r.Name));
                        }
                    }
                    foreach (IFieldDescriptor fd in structure.FieldDescriptors)
                    {
                        fields.Add(new Field(fd, reader[fd.FieldName] is DBNull ? null : reader[fd.FieldName]));
                    }
                    result.Add(new Row(fields, relations));
                }
            }
            return result;
        }

        #endregion

        
		#region Aux

        public string Enquote(string str)
        {
            return String.Format("[{0}]", str);
        }

        public string Unquote(string str)
        {
            char[] charsToTrim = { '[', ']' };
            return str.Trim(charsToTrim);
        }

        public IEnumerable<IRowDescriptor> GetAllTablesStructure(string connectionString)
        {
            List<IRowDescriptor> result = new List<IRowDescriptor>();
            foreach (string tableName in GetAllTablesName(connectionString))
                result.Add(SelectTableStructure(connectionString, tableName));
            return result;
        }

        public IEnumerable<string> GetAllTablesName(string connectionString)
        {
            List<string> result = new List<string>();
            SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder(connectionString);

            SqlConnection dbmsCon = new SqlConnection(connectionString);
            SqlCommand cmd = null;
            SqlDataReader reader = null;

            try
            {
                dbmsCon.Open();
                cmd = new SqlCommand(String.Format(@"SELECT TABLE_NAME
                                                        FROM INFORMATION_SCHEMA.TABLES
                                                        WHERE TABLE_CATALOG = '{0}' AND TABLE_SCHEMA = 'dbo' AND TABLE_TYPE = 'BASE TABLE'", csb.InitialCatalog), dbmsCon);
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    result.Add((string)reader["TABLE_NAME"]);
                }
            }
            //catch
            //{
            //writing in logs
            //...
            //showing the message
            //}
            finally
            {
                if (reader != null) reader.Dispose();
                if (cmd != null) cmd.Dispose();
                if (dbmsCon != null) dbmsCon.Dispose();
            }
            return result;
        }

	    #endregion
    }

}
